package day.statistic;

import entity.day.StatDay;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.database.SqlServerAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Created by lit on 2018/4/13.
 */
public class ProcessStatDay {
    //数据连接
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;

    private Logger logger = null;

    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void processStatDay(String[] dayStatTime){
        logger = LoggerFactory.getLogger(ProcessStatDay.class);
        /*String latestTime = getLatestTime();*/
        clearStatDay(dayStatTime);
        ArrayList<StatDay> statDaysArray = getStatDay(dayStatTime);
        addStatDay(statDaysArray);
        finishMSSQL();
    }

    private void clearStatDay(String[] dayStatTime) {
        if(initMSSQL()){
            String sql = "DELETE FROM stat_day where StatDate = ?";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,dayStatTime[0]);
                int num = preparedStatement.executeUpdate();
                connection.commit();
                if(num > 0){
                    logger.info("清除{}重复数据{}条",dayStatTime[0],num);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 3.数据插入StatDay数据表
     * @param statDaysArray
     */
    private void addStatDay(ArrayList<StatDay> statDaysArray) {
        if(initMSSQL()){
            String sql = "insert into stat_day (StatDate,DailyTrip,DailyVehicle,DailyLength,DailyTime,AmPeakTrip,AmPeakVehicle,PmPeakTrip,PmPeakVehicle) values (?,?,?,?,?,?,?,?,?)";
            try{
                int num = 0;
                preparedStatement = connection.prepareStatement(sql);
                for(int i=0; i < statDaysArray.size(); i++){
                    StatDay statDay = statDaysArray.get(i);
                    preparedStatement.setString(1,statDay.getTripTime());
                    preparedStatement.setInt(2,statDay.getDailyTrip());
                    preparedStatement.setInt(3,statDay.getDailyVehicle());
                    preparedStatement.setLong(4,statDay.getDailyLength());
                    preparedStatement.setInt(5,statDay.getDailyTime());
                    preparedStatement.setInt(6,statDay.getAmPeakTrip());
                    preparedStatement.setInt(7,statDay.getAmPeakVehicle());
                    preparedStatement.setInt(8,statDay.getPmPeakTrip());
                    preparedStatement.setInt(9,statDay.getPmPeakVehicle());
                    preparedStatement.addBatch();
                    if((i+1)%100 == 0){
                        preparedStatement.executeBatch();
                    }
                    num++;
                }
                preparedStatement.executeBatch();
                connection.commit();
                logger.info("日统计插入数据{}条",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 2.获取数据
     * @param dayStatTime
     * @return
     */
    private ArrayList<StatDay> getStatDay(String[] dayStatTime) {
        if (initMSSQL()){
            ArrayList<StatDay> statDaysArray = new ArrayList<>();
            String sql = "SELECT t1.TripTime,DailyTrip,DailyVehicle,DailyLength,DailyTime,AmPeakTrip,AmPeakVehicle,PmPeakTrip,PmPeakVehicle  FROM";
            sql += " (SELECT CONVERT(VARCHAR (10),TripStartTime,21) as TripTime,COUNT (TripID) as DailyTrip,COUNT (DISTINCT VID) as DailyVehicle,SUM (TripLen) as DailyLength,SUM (TripTime) as DailyTime FROM rtm_trip " +
                    " WHERE TripStartTime >= ? AND TripStartTime < ? GROUP BY CONVERT(VARCHAR (10),TripStartTime,21))t1";
            sql +=  " LEFT JOIN " +
                    " (SELECT CONVERT(VARCHAR (10),TripStartTime,21) as TripTime,COUNT (TripID) as AmPeakTrip,COUNT (DISTINCT VID) as AmPeakVehicle FROM rtm_trip " +
                    " WHERE TripStartTime >= ? AND TripStartTime < ? " +
                    " AND DATENAME(HOUR, TripStartTime) >=7 AND DATENAME(HOUR, TripStartTime)< 9 " +
                    " GROUP BY CONVERT(VARCHAR (10),TripStartTime,21))t2 " +
                    " ON t1.TripTime = t2.TripTime";
            sql += " LEFT JOIN " +
                    " (SELECT CONVERT(VARCHAR (10),TripStartTime,21) as TripTime,COUNT (TripID) as PmPeakTrip,COUNT (DISTINCT VID) as PmPeakVehicle " +
                    " FROM rtm_trip " +
                    " WHERE TripStartTime >= ? AND TripStartTime < ? " +
                    " AND DATENAME(HOUR, TripStartTime) >= 17 AND DATENAME(HOUR, TripStartTime)< 19 " +
                    " GROUP BY CONVERT(VARCHAR (10),TripStartTime,21))t3 " +
                    " ON t1.TripTime = T3.TripTime";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,dayStatTime[0]);
                preparedStatement.setString(2,dayStatTime[1]);
                preparedStatement.setString(3,dayStatTime[0]);
                preparedStatement.setString(4,dayStatTime[1]);
                preparedStatement.setString(5,dayStatTime[0]);
                preparedStatement.setString(6,dayStatTime[1]);
                ResultSet rs = preparedStatement.executeQuery();
                while(rs.next()){
                    StatDay statDay = new StatDay();
                    statDay.setTripTime(rs.getString("TripTime"));
                    statDay.setDailyTrip(rs.getInt("DailyTrip"));
                    statDay.setDailyVehicle(rs.getInt("DailyVehicle"));
                    statDay.setDailyLength(rs.getLong("DailyLength"));
                    statDay.setDailyTime(rs.getInt("DailyTime"));
                    statDay.setAmPeakTrip(rs.getInt("AmPeakTrip"));
                    statDay.setAmPeakVehicle(rs.getInt("AmPeakVehicle"));
                    statDay.setPmPeakTrip(rs.getInt("PmPeakTrip"));
                    statDay.setPmPeakVehicle(rs.getInt("PmPeakVehicle"));
                    statDaysArray.add(statDay);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            return statDaysArray;
        }
        return null;
    }

    /**
     * 1.获取日统计最新需要处理的日期
     * @return
     */
    private String getLatestTime() {
        if(initMSSQL()){
            String sql = "SELECT TOP 1 DATEADD(DAY,1,StatDate) as latestTime FROM stat_day ORDER BY StatDate DESC";
            try{
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
                if(rs.next()){
                    return rs.getString("latestTime");
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }
}
